/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.Timestamp;
import entity.*;
import utils.DBHelper;

/**
 * @author ZYS
 *
 */
public class ReplyDAO {
	private ReplyDAO() {
	}

	public static ReplyDAO getInstance() {
		return new ReplyDAO();
	}
	/**
	 * 得到读者反馈总数
	 *
	 */
	public int getTotal() {
		int total = 0;
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select count(*) from reply";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}

			System.out.println("total:" + total);

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return total;
	}
	/**
	 * 新增读者反馈
	 *
	 */
	public void add(Reply reply) {

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "insert into reply values(null,?,?,?,?)";
			PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		
			ps.setInt(1, reply.getUser_id());
			ps.setString(2, reply.getUser_name());
			ps.setString(3, reply.getContent());
			ps.setTimestamp(4, reply.getReplytime());
			
			ps.execute();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int r_id =  rs.getInt(1);
				reply.setR_id(r_id);
			}

			DBHelper.closeConnection(c, ps, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 删除公告
	 *
	 */
	public void delete(int r_id) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "delete from reply where r_id = " + r_id;

			s.execute(sql);

			DBHelper.closeConnection(c, s, null);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 通过读者反馈id得到反馈对象
	 *
	 */
	public Reply get(int r_id) {
		Reply reply = null;
		try {
			
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from reply where r_id = " + r_id;

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {

				reply = new Reply();
				
				String content = rs.getString("content");
				int user_id=rs.getInt("user_id");
				String user_name=rs.getString("user_name");
				Timestamp replytime=rs.getTimestamp("replytime");
				reply.setR_id(r_id);
				reply.setContent(content);
				reply.setReplytime(replytime);
				reply.setUser_id(user_id);
				reply.setUser_name(user_name);
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return reply;
	}
	
	
	
	/**
	 * 得到用户反馈列表
	 *
	 */
	public List<Reply> list() {
		return list(0, Short.MAX_VALUE);
	}
	/**
	 * 得到从start开始count个读者反馈
	 *
	 */
	public List<Reply> list(int start, int count) {
		List<Reply> replys = new ArrayList<Reply>();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from reply order by r_id limit ?,? ";

			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Reply reply = new Reply();
				int r_id = rs.getInt("r_id");
				String content = rs.getString("content");
				int user_id=rs.getInt("user_id");
				String user_name=rs.getString("user_name");
				Timestamp replytime=rs.getTimestamp("replytime");
				reply.setR_id(r_id);
				reply.setContent(content);
				reply.setReplytime(replytime);
				reply.setUser_id(user_id);
				reply.setUser_name(user_name);
				
				
				replys.add(reply);
			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return replys;
	}
	/**
	 * 得到所有的
	 * @return
	 */
	public static List<Reply> getAll(){
		List<Reply> replylist = new ArrayList<Reply>();
		try {
			Reply reply = null;
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from reply";

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {

				reply = new Reply();
				int r_id = rs.getInt("r_id");
				String content = rs.getString("content");
				int user_id=rs.getInt("user_id");
				String user_name=rs.getString("user_name");
				Timestamp replytime=rs.getTimestamp("replytime");
				reply.setR_id(r_id);
				reply.setContent(content);
				reply.setReplytime(replytime);
				reply.setUser_id(user_id);
				reply.setUser_name(user_name);
				replylist.add(reply);
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return replylist;
		
	}
}
